package com.quanxi.qxaimain.common.util.execlExporttool;

/**
 * @Author lyx
 * @create 2022/11/3 10:34
 */


import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.*;
import java.util.*;

/**
 * SXSSFWorkbook导出
 *
 * @date 2022-05-30 10:50
 */
@Slf4j
public class SXSSFWorkbookExporter {

    /**
     * 表头
     */
    private String[] headerNames;
    /**
     * 工作簿
     */
    private Workbook workBook;
    /**
     * excel工作表
     */
    private Sheet sheet;


    private String sheetname;

    /**
     *
     * @param headerNames 表头
     * @param sheetName sheet的名称
     */
    public SXSSFWorkbookExporter(String[] headerNames, String sheetName) {
        this.headerNames = headerNames;
        // 创建一个工作簿 内存中保持100条数据, 超出的部分刷新到磁盘上
        workBook = new SXSSFWorkbook(100);
        // 创建一个工作表sheet
  //      sheet = workBook.createSheet(sheetName);
    //    initHeader();
        this.sheetname=sheetName;
    }

    /**
     * 初始化表头信息
     */
    private void initHeader()  {

        // 创建第一行
        Row row = sheet.createRow(0);
        Cell cell = null;
        // 创建表头
        for (int i = 0; i < headerNames.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headerNames[i]);
            setHeaderCellStyle(cell);
        }
    }

    /**
     * 设置表头单元格样式
     *
     * @param cell 单元格
     */
    public void setHeaderCellStyle(Cell cell) {
        // 设置样式
        CellStyle cellStyle = workBook.createCellStyle();
        // 设置字体居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置字体
        Font font = workBook.createFont();
        font.setFontName(HSSFFont.FONT_ARIAL);
        // 设置加粗
        font.setBold(true);
        // 设置字号
        font.setFontHeightInPoints((short) 13);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    /**
     *
     * @param datas 数据,每一个map都是一行
     * @param keys key[i]代表从map中获取keys[i]的值作为第i列的值,如果传的是null默认取表头
     */
    public void createTableRows(List<Map<String, Object>> datas, String[] keys) {

        int rowNo = 0;		//总行号
        int pageRowNo = 0;	//页行号


        for (int i = 0, length_1 = datas.size(); i < length_1; i++) {
//            if (ArrayUtils.isEmpty(keys)) {
//                keys = headerNames;
//            }
            if(rowNo%1000000==0){
                log.info("第"+rowNo/1000000+1+"个1000000行处理完成......");
                sheet = workBook.createSheet(sheetname+"("+(rowNo/1000000+1)+")");
                sheet = workBook.getSheetAt(rowNo/1000000);		//动态指定当前的工作表 每个sheet存放1000000行数据
                initHeader();
                pageRowNo = 1;
            }

            rowNo++;


            // 创建行(从第二行开始)
            Map<String, Object> data = datas.get(i);
            Row row = sheet.createRow(pageRowNo++);
            Cell cell = null;
            for (int j = 0, length_2 = keys.length; j < length_2; j++) {
                // 单元格获取map中的key
                String key = keys[j];
                String value = MapUtils.getString(data, key, "");
                cell = row.createCell(j);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(value);
            }

        }
    }

    /**
     * 根据表头自动调整列宽度
     */
    public void autoAllSizeColumn() {
        // 如果是SXSSFSheet，需要调用trackAllColumnsForAutoSizing方法一次
        if (sheet instanceof SXSSFSheet) {
            SXSSFSheet tmpSheet = (SXSSFSheet) sheet;
            tmpSheet.trackAllColumnsForAutoSizing();
        }
        for (int i = 0, length = headerNames.length; i < length; i++) {
            sheet.autoSizeColumn(i);//根据表头自动列宽
       //      sheet.setColumnWidth(i,sheet.getColumnWidth(i)*18/10);// 根据内容自动列宽
        }
    }

    /**
     * 将数据写出到excel中
     *
     * @param outputStream
     */
    public void exportExcel(OutputStream outputStream) {
        // 导出之前先自动设置列宽
        this.autoAllSizeColumn();
        try {
            workBook.write(outputStream);
        } catch (IOException e) {
         //   log.error(" exportExcel error", e);
        } finally {
            IOUtils.closeQuietly(outputStream);
        }


    }

    /**
     * 将数据写出到excel中
     *
     * @param outputFilePath
     */
    public void exportExcel(String outputFilePath) {
        // 导出之前先自动设置列宽
        this.autoAllSizeColumn();
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(outputFilePath);
            workBook.write(outputStream);
        } catch (IOException e) {
          log.error(" exportExcel error", e);
        } finally {
            IOUtils.closeQuietly(outputStream);
        }
    }

    public static void main(String[] args) {
        test();
    }

    private static void test() {

        List<Map<String, Object>> datas = new ArrayList();
        for (int i = 0; i < 201; i++) {
            Map data = new HashMap();
            data.put("name", "name" + i);
            data.put("age", "age" + i);
            datas.add(data);
        }

        if(datas.isEmpty() ||datas==null){
            Map<String, Object> map =new HashMap<String, Object>();
            map.put("EXECL名称","无数据，请检查搜索条件");
            datas.add(map);
        }

        Map<String, Object> map = datas.get(0);
        Set<String> set = map.keySet();
        List<String> list = new ArrayList(set);
        String[] headname = new String[list.size()];
        list.toArray(headname ); //获取Excel列名


        /**
         * 将List<Map<String, Object>>写出到excel中
         */
        SXSSFWorkbookExporter hssfWorkExcel = new SXSSFWorkbookExporter( headname, "EXECL名称");//初始化execl
        hssfWorkExcel.createTableRows(datas, headname);//插入数据
        try {
            hssfWorkExcel.exportExcel(new FileOutputStream(new File("F:\\360MoveData\\Users\\lyx\\Desktop\\bigXmlConvertDB-master\\人员基本信息.xlsx"))); //导出数据
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

    }

}
